/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package anton.dinner;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import javax.annotation.Resource;
import javax.sql.DataSource;
import javax.inject.Named;
import javax.enterprise.context.SessionScoped;

/**
 *
 * @author johan
 */
@Named(value = "listSpecIngredients")
@SessionScoped
public class ListSpecIngredients implements Serializable {
    

    /**
     * Creates a new instance of DinnerIngredients
     */
    
    @Resource(name="jdbc/antonsDatasource")
    
	private DataSource datasource;

    public void setDatasource(DataSource datasource) {
        this.datasource = datasource;
    }
   
        private List<String> list;
        private String selectedValue;
        
        
   // used to get selected value in list
    public String getSelectedValue() {
        return selectedValue;
    }
    // used to set selected value
    public void setSelectedValue(String selectedValue) {
        this.selectedValue = selectedValue;
    }
    
    
   public List<SpecIngredientsEntity> getDinnerIngredients() throws SQLException{
 
       
		if(datasource==null)
			throw new SQLException("Can't get data source");// throws exception i database not available
 
		//database connection
		Connection con = datasource.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
		PreparedStatement ps 
			= con.prepareStatement(
			   "select item_id, item_name from Stock where item_rarity=1"); // query select from stock "rare" item  later used to display existing rare items 
 
		//get  item info from database
		ResultSet result =  ps.executeQuery();
 
		List<SpecIngredientsEntity> list = new ArrayList<>();
 
		while(result.next()){
			
                SpecIngredientsEntity ingredients = new SpecIngredientsEntity();
			
                ingredients.setItem_id(result.getString("item_id"));
		ingredients.setItem_name(result.getString("item_name"));
 
			//adds ingredients to list
			list.add(ingredients);
		}
                    con.close();
		return list;
                
	}
    
   
   /*select Dinner.dinner_name,Stock.item_ID,item_name from Stock, DinnerIngredients, Dinner where Dinner.dinner_name = 
   DinnerIngredients.dinner_name and Stock.item_Id = DinnerIngredients.item_Id and  Dinner.dinner_name ='Lyxpytt';*/
   
    public List<String> getSelectedIngredients(String dinnerName) throws SQLException{
        
                String sql="select Dinner.dinner_name,Stock.item_ID,item_name from Stock, DinnerIngredients, Dinner where Dinner.dinner_name = DinnerIngredients.dinner_name and Stock.item_Id = DinnerIngredients.item_Id and  Dinner.dinner_name ='"+dinnerName+"'";
                
       
		if(datasource==null)
			throw new SQLException("Can't get data source");// throws exception i database not available
                
                
        try (Connection con = datasource.getConnection()) {
            if(con==null)
                throw new SQLException("Can't get database connection");
            PreparedStatement ps
                    = con.prepareStatement(sql);
            ResultSet result =  ps.executeQuery();
            list = new ArrayList<>();
            while(result.next()){
                     
			
                String item=result.getString("item_id");
                
                
                //adds item_id to list
                list.add(item);
                
                
            }
        }
		return list;
                
	}
    
    
     public void setList(List<String> list) {
        this.list = list;
    }
     
     
      public List<SpecIngredientsEntity> getSelectedIngredientsEntities(String dinnerName) throws SQLException{
        
                String sql="select Dinner.dinner_name,Stock.item_ID,item_name from Stock, DinnerIngredients, Dinner where Dinner.dinner_name = DinnerIngredients.dinner_name and Stock.item_Id = DinnerIngredients.item_Id and  Dinner.dinner_name ='"+dinnerName+"'";
                ArrayList<SpecIngredientsEntity> specList;
                SpecIngredientsEntity sEntity; 
       
		if(datasource==null)
			throw new SQLException("Can't get data source");// throws exception i database not available
                
                
        try (Connection con = datasource.getConnection()) {
            if(con==null)
                throw new SQLException("Can't get database connection");
            PreparedStatement ps
                    = con.prepareStatement(sql);
            ResultSet result =  ps.executeQuery();
          specList = new ArrayList<>();
           
            while(result.next()){
                
              sEntity = new SpecIngredientsEntity();   
			
                sEntity.setItem_name(result.getString("item_Name"));
                sEntity.setItem_id(result.getString("item_ID"));
                
                //adds item_id to list
                specList.add(sEntity);
                
                
            }
        }
		return specList;
                
	}
     
     
   
}
    
